--EXEC SP_CREATE_UPDATE_CUSTOMER_SITIME '1020101221', 'T1ESTI2N22G1', 'MAINADDRESS', 'MAINADDRESS', 'NET30', 1000
ALTER PROC SP_CREATE_UPDATE_CUSTOMER_SITIME
@CUSTOMER_NUMBER   	VARCHAR( 50 ),
@CUSTOMER_NAME    	VARCHAR( 50 ),
@DEFAULT_BILLING_ADDRESS VARCHAR( 50 ),
@DEFAULT_SHIPPING_ADDRESS VARCHAR( 50 ),
@PAYMENT_TERMS            VARCHAR( 50 ),
@CREDIT_LIMIT INT,
@CUSTOMER_HOLD INT
as
begin
SET NOCOUNT ON
	DECLARE @CUSTOMER_ID INT 
	DECLARE @DEFAULT_SHIPPING_ADDRESS_ID INT
	DECLARE @DEFAULT_BILLING_ADDRESS_ID INT
	DECLARE @FIN_HOLD INT 
	DECLARE @APPROVED_ORDER_COUNT INT
	SELECT @CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_Number = @CUSTOMER_NUMBER AND C_Disabled = 1

	IF @PAYMENT_TERMS = 'Prepayment' OR @CUSTOMER_HOLD = 1 OR @PAYMENT_TERMS = 'Credit Card'
	BEGIN
		SET @FIN_HOLD = 1
	END
	ELSE
	BEGIN
		SET @FIN_HOLD = 0
	END
	
	
	IF @CUSTOMER_ID IS NULL
	BEGIN
		INSERT INTO ADDRESS ( ADDRESS_1, TAG_NAME ) VALUES ( @CUSTOMER_NAME, @DEFAULT_SHIPPING_ADDRESS )
		SELECT @DEFAULT_SHIPPING_ADDRESS_ID = @@IDENTITY
		
		INSERT INTO ADDRESS ( ADDRESS_1, TAG_NAME ) VALUES ( @CUSTOMER_NAME, @DEFAULT_BILLING_ADDRESS )
		SELECT @DEFAULT_BILLING_ADDRESS_ID = @@IDENTITY

		INSERT INTO CUSTOMER ( C_NAME, C_NUMBER, C_PAY_TERMS, C_CREDIT_LIMIT, C_Default_Shipping_Address_ID, C_Default_Billing_Address_ID, C_DISABLED )
		VALUES ( @CUSTOMER_NAME, @CUSTOMER_NUMBER, @PAYMENT_TERMS, @CREDIT_LIMIT, @DEFAULT_SHIPPING_ADDRESS_ID, @DEFAULT_BILLING_ADDRESS_ID, 1 )
		SELECT @CUSTOMER_ID = @@IDENTITY

		INSERT INTO Customer_Type_Relationship VALUES ( @CUSTOMER_ID, 1 )

		--INSERT INTO Customer_Shipping_Address_Table VALUES ( @CUSTOMER_ID, @DEFAULT_SHIPPING_ADDRESS_ID, CURRENT_TIMESTAMP )
		--INSERT INTO CUSTOMER_BILLING_ADDRESS_TABLE VALUES ( @CUSTOMER_ID, @DEFAULT_BILLING_ADDRESS_ID, CURRENT_TIMESTAMP )

		INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Is_Approved, Hold_By_User, Timestamp, IS_SAMPLE ) 
		VALUES ( @CUSTOMER_ID, 0, 'OPERATIONS', 1, 'admin', CURRENT_TIMESTAMP, 1 )
		
		IF @FIN_HOLD = 0
		BEGIN
			INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Is_Approved, Hold_By_User, Timestamp, IS_SAMPLE ) 
			VALUES ( @CUSTOMER_ID, '', 'FINANCE', 0, 'admin', CURRENT_TIMESTAMP, 1 )
		END
		ELSE
		BEGIN
			INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Is_Approved, Hold_By_User, Timestamp, IS_SAMPLE ) 
			VALUES ( @CUSTOMER_ID, 0, 'FINANCE', 1, 'admin', CURRENT_TIMESTAMP, 1 )
		END
	
		INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Is_Approved, Hold_By_User, Timestamp, IS_SAMPLE ) 
		VALUES ( @CUSTOMER_ID, '', 'MARKETING', 0, 'admin', CURRENT_TIMESTAMP, 1)
		INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Is_Approved, Hold_By_User, Timestamp, IS_SAMPLE ) 
		VALUES ( @CUSTOMER_ID, 0, 'SALES', 1, 'admin', CURRENT_TIMESTAMP, 1)

	END
	ELSE
	BEGIN
		--SELECT @DEFAULT_SHIPPING_ADDRESS_ID = CSAT.Address_ID FROM Customer_Shipping_Address_Table CSAT, ADDRESS A WHERE CSAT.Customer_ID = @CUSTOMER_ID AND A.TAG_NAME = @DEFAULT_SHIPPING_ADDRESS

		SELECT @DEFAULT_SHIPPING_ADDRESS_ID = ADDRESS_ID FROM ADDRESS WHERE ADDRESS_ID IN ( SELECT C_Default_Shipping_Address_ID FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID UNION SELECT ADDRESS_ID FROM Customer_Shipping_Address_Table WHERE CUSTOMER_ID = @CUSTOMER_ID ) AND TAG_NAME = @DEFAULT_SHIPPING_ADDRESS
		IF @DEFAULT_SHIPPING_ADDRESS_ID IS NULL OR @DEFAULT_SHIPPING_ADDRESS_ID < 1
		BEGIN
			INSERT INTO ADDRESS ( ADDRESS_1, TAG_NAME ) VALUES ( @CUSTOMER_NAME, @DEFAULT_SHIPPING_ADDRESS )
			SELECT @DEFAULT_SHIPPING_ADDRESS_ID = @@IDENTITY
		END

		--SELECT @DEFAULT_BILLING_ADDRESS_ID = CBAT.Address_ID FROM CUSTOMER_BILLING_ADDRESS_TABLE CBAT, ADDRESS A WHERE CBAT.Customer_ID = @CUSTOMER_ID AND A.TAG_NAME = @DEFAULT_BILLING_ADDRESS

		SELECT @DEFAULT_BILLING_ADDRESS_ID = ADDRESS_ID FROM ADDRESS WHERE ADDRESS_ID IN ( SELECT C_Default_Billing_Address_ID FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID UNION SELECT ADDRESS_ID FROM CUSTOMER_BILLING_ADDRESS_TABLE WHERE CUSTOMER_ID = @CUSTOMER_ID ) AND TAG_NAME = @DEFAULT_BILLING_ADDRESS
		IF @DEFAULT_BILLING_ADDRESS_ID IS NULL OR @DEFAULT_BILLING_ADDRESS_ID < 1
		BEGIN
			INSERT INTO ADDRESS ( ADDRESS_1, TAG_NAME ) VALUES ( @CUSTOMER_NAME, @DEFAULT_BILLING_ADDRESS )
			SELECT @DEFAULT_BILLING_ADDRESS_ID = @@IDENTITY
		END
	
		UPDATE CUSTOMER SET C_NAME = @CUSTOMER_NAME, C_PAY_TERMS = @PAYMENT_TERMS, C_CREDIT_LIMIT = @CREDIT_LIMIT, C_Default_Shipping_Address_ID = @DEFAULT_SHIPPING_ADDRESS_ID, C_Default_Billing_Address_ID = @DEFAULT_BILLING_ADDRESS_ID, C_DISABLED = 1  WHERE C_Number = @CUSTOMER_NUMBER AND C_DISABLED = 1 

		IF (  ( SELECT COUNT(*) FROM Customer_Type_Relationship WHERE CUSTOMER_ID =  @CUSTOMER_ID AND C_TYPE_ID = 1 ) = 0  )
		BEGIN
			INSERT INTO Customer_Type_Relationship VALUES ( @CUSTOMER_ID, 1 )
		END

		IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'OPERATIONS' AND IS_SAMPLE = 1 ) = 0 )
		BEGIN
			INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Is_Approved, Hold_By_User, Timestamp, IS_SAMPLE ) 
			VALUES ( @CUSTOMER_ID, 0, 'OPERATIONS', 1, 'admin', CURRENT_TIMESTAMP, 1 )
		END
		ELSE IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'OPERATIONS' AND IS_SAMPLE = 1 ) > 0 )
		BEGIN
			UPDATE Customer_Hold_Table SET Hold_Reason_Code = 0, Is_Approved = 1 , Timestamp = CURRENT_TIMESTAMP 
			WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'OPERATIONS'  AND IS_SAMPLE = 1
		END


		IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'FINANCE' AND IS_SAMPLE = 1 ) = 0 )
		BEGIN
			IF @FIN_HOLD = 0
			BEGIN
				INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Is_Approved, Hold_By_User, Timestamp, IS_SAMPLE ) 
				VALUES ( @CUSTOMER_ID, '', 'FINANCE', 0, 'admin', CURRENT_TIMESTAMP, 1 )
			END
			ELSE
			BEGIN
				INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Is_Approved, Hold_By_User, Timestamp, IS_SAMPLE ) 
				VALUES ( @CUSTOMER_ID, 0, 'FINANCE', 1, 'admin', CURRENT_TIMESTAMP, 1 )
			END
		END
		ELSE IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'FINANCE' AND IS_SAMPLE = 1 ) > 0 )
		BEGIN
			IF @FIN_HOLD = 0
			BEGIN
				UPDATE Customer_Hold_Table SET Hold_Reason_Code = '',Is_Approved = 0 , Timestamp = CURRENT_TIMESTAMP 
				WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'FINANCE'  AND IS_SAMPLE = 1
			END
			ELSE
			BEGIN
				UPDATE Customer_Hold_Table SET Hold_Reason_Code = 0,Is_Approved = 1, Timestamp = CURRENT_TIMESTAMP 
				WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'FINANCE'  AND IS_SAMPLE = 1
			END
		END

		IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'MARKETING' AND IS_SAMPLE = 1 ) = 0 )
		BEGIN
			INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Is_Approved, Hold_By_User, Timestamp, IS_SAMPLE ) 
			VALUES ( @CUSTOMER_ID, '', 'MARKETING', 0, 'admin',CURRENT_TIMESTAMP, 1)
		END 
		ELSE IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'MARKETING' AND IS_SAMPLE = 1 ) > 0 )
		BEGIN
			UPDATE Customer_Hold_Table SET Hold_Reason_Code = '', IS_Approved = 0, Timestamp = CURRENT_TIMESTAMP 
			WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'MARKETING'  AND IS_SAMPLE = 1
		END

		IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'SALES' AND IS_SAMPLE = 1 ) = 0 )
		BEGIN
			INSERT INTO Customer_Hold_Table( Customer_ID, Hold_Reason_Code, Hold_By_Group, Is_Approved, Hold_By_User, Timestamp, IS_SAMPLE ) 
			VALUES ( @CUSTOMER_ID, 0, 'SALES', 1, 'admin', CURRENT_TIMESTAMP, 1)
		END
		ELSE IF( ( SELECT COUNT(*) FROM Customer_Hold_Table WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'SALES' AND IS_SAMPLE = 1 ) > 0 )
		BEGIN
			UPDATE Customer_Hold_Table SET Hold_Reason_Code = 0, Is_Approved = 1 ,Timestamp = CURRENT_TIMESTAMP 
			WHERE CUSTOMER_ID = @CUSTOMER_ID AND Hold_By_Group = 'SALES'  AND IS_SAMPLE = 1
		END
	END		
	SET @APPROVED_ORDER_COUNT = 0
	IF (@CUSTOMER_HOLD = 1) 
	BEGIN
		SELECT @APPROVED_ORDER_COUNT = COUNT(*) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.CUSTOMER_ID = @CUSTOMER_ID AND COOL_STATUS = 'APPROVED'
		IF @APPROVED_ORDER_COUNT IS NOT NULL AND @APPROVED_ORDER_COUNT > 0
		BEGIN
			UPDATE Sales_hold_Table SET HOLD_REASON_CODE = 0, IS_APPROVED = 1 WHERE LINEITEM_ID IN ( SELECT COOL.LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, SALES_ORDER SO WHERE COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.CUSTOMER_ID = @CUSTOMER_ID AND COOL_STATUS = 'APPROVED'  )
		END
	END
END